package dataAccessLayer.factoryMySQL.daos;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import dataAccessLayer.abstractFactory.daos.DAOcities;
import dataAccessLayer.dataBase.DBManager;
import dataAccessLayer.visualObject.VOcity;

public class DAOcitiesMySQL implements DAOcities
{
	private DBManager dbManager;

	public DAOcitiesMySQL(DBManager dbManager) 
	{
		this.dbManager = dbManager;
	}

	public int insertCity(VOcity vOcity) 
	{
		int code = -1;

		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;

		Connection connection = dbManager.getConnection();

		try
		{
			preparedStatement = connection.prepareStatement("INSERT INTO cities (name) VALUES (?)");

			preparedStatement.setString(1, vOcity.getName());

			preparedStatement.execute();

			preparedStatement = connection.prepareStatement("SELECT code FROM cities ORDER BY code DESC LIMIT 1");

			resultSet = preparedStatement.executeQuery();

			resultSet.next();

			code = resultSet.getInt(1);
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				
				preparedStatement.close();
				resultSet.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		return code;
	}

	public boolean updateCity(VOcity vOcity) 
	{
		PreparedStatement preparedStatement = null;

		Connection connection = dbManager.getConnection();
		
		try {
			preparedStatement = connection.prepareStatement("UPDATE cities set name=? where code=?");
			preparedStatement.setString(1,vOcity.getName());
			preparedStatement.setInt(2, vOcity.getCode());
			
			preparedStatement.execute();
			
			return true;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			try
			{
				
				preparedStatement.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		
		return false;
	}

	public boolean removeCity(VOcity vOcity) 
	{
		PreparedStatement preparedStatement = null;

		Connection connection = dbManager.getConnection();
		
		try {
			preparedStatement = connection.prepareStatement("DELETE from cities where code=?");
			preparedStatement.setInt(1, vOcity.getCode());
			
			preparedStatement.execute();
			
			return true;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally
		{
			try
			{
				
				preparedStatement.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		
		return false;
	}

	public ArrayList<VOcity> listCities() 
	{
		
		PreparedStatement preparedStatement = null;
		ResultSet resulSet=null;
		Connection connection = dbManager.getConnection();
		ArrayList<VOcity> searchCities=new ArrayList<VOcity>();
		
		
		try {
			preparedStatement = connection.prepareStatement("SELECT * from cities");			
			resulSet=preparedStatement.executeQuery();
			while(resulSet.next())
			{
				VOcity search=new VOcity();
				search.setCode(resulSet.getInt(1));
				search.setName(resulSet.getString(2));
				searchCities.add(search);
			}
			
			return searchCities;
			
		} catch (SQLException e) {
			e.printStackTrace();
		}	
		finally
		{
			try
			{
				
				preparedStatement.close();
				resulSet.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		
		return null;
	}

	public VOcity searchCityByCode(VOcity vOcity) 
	{
		PreparedStatement preparedStatement = null;
		ResultSet resulSet = null;
		Connection connection = dbManager.getConnection();

		VOcity citySearch = null;

		try
		{
			preparedStatement = connection.prepareStatement("SELECT code,name FROM cities WHERE code=?");

			preparedStatement.setInt(1, vOcity.getCode());

			resulSet = preparedStatement.executeQuery();
			
			resulSet.next();

			citySearch = new VOcity();
			citySearch.setCode( resulSet.getInt(1) );
			citySearch.setName( resulSet.getString(2) );
			
		}
		catch (SQLException e)
		{
			e.printStackTrace();
		}
		finally
		{
			try
			{
				
				preparedStatement.close();
				resulSet.close();
			}
			catch (SQLException e)
			{
				e.printStackTrace();
			}
		}
		
		return citySearch;
	}

	public VOcity searchCityByName(VOcity vOcity) 
	{
		return null;
	}

}
